﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;
using System.Data;
using System.Net;
using System.Collections;
using System.IO;
using System.Xml;
using System.Data.OleDb;

namespace SQLCLR
{
    class Program
    {
        static void Main()
        {
            string connectionstring = " Provider = Microsoft.JeT.OLEDB.4.0 ; Data Source = D:\\test.xlsx ; Extended Properties =Excel 8.0 ";

            OleDbConnection conn = new OleDbConnection(connectionstring);
            conn.Open();
            DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null,null,null, " Table "});
            string sql = "select * from Sheet1$";
            OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp.Fill(ds);

        }
        static void Main2(string[] args)
        {
            string reportId = "1678BC61-3909-4F01-8886-9F66DDE3A3D1";
            string date = "2011-01-01";
            string ouid = "4A4B0B90-F223-4D1B-87CE-790C85BE54FF";
            string url = "http://ppsv2.petrochina/WebService/ExcelReportWS.asmx/GetReportFile?ReportID=" + reportId + "&rtype=24315&dt=" + date + "&OUID=" + ouid;
            
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(GetWebService(url));
            
            byte[] inputBytes = Convert.FromBase64String(doc.InnerText);
            Stream stream = new MemoryStream(inputBytes);
            Workbook wk = new Workbook(stream);
            Cells cells = wk.Worksheets[0].Cells;
            DataTable dt = wk.Worksheets[0].Cells.ExportDataTableAsString(0, 0, cells.MaxRow + 1, cells.MaxColumn, true);

        }

        public static string GetWebService(string URL)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(URL);
            StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream(), Encoding.UTF8);
            string retXml = sr.ReadToEnd();
            sr.Close();
            return retXml;
        }
        public static void StreamToFile(Stream stream, string fileName)
        {
            // 把 Stream 转换成 byte[]
            byte[] bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);
            // 设置当前流的位置为流的开始
            stream.Seek(0, SeekOrigin.Begin);
            // 把 byte[] 写入文件
            FileStream fs = new FileStream(fileName, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(bytes);
            bw.Close();
            fs.Close();
        }

    }
}
